package com.hualude.roadsign.repository;

import com.hualude.roadsign.dto.DTRequestDto;
import com.hualude.roadsign.dto.FaultCountDto;
import com.hualude.roadsign.dto.SignalLampDto;
import com.hualude.roadsign.dto.SignalLampFaultDto;
import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

import static com.hualude.roadsign.service.impl.CountServiceImpl.convertParams;


/**
 * Created by ding on 2017/9/21.
 */
public class SignalLampFaultCountRepositoryImpl {

    @PersistenceContext
    private EntityManager em;

    public List<String> getDealCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.LampMaintenanceTime from tb_lamp_fault m,tb_user u where m.LampMaintenanceStatus = 2 and " +
                "(m.LampMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.LampMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }
    public Integer getLampCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_signal_lamp where SignalLampId like '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:起始路段 4:终止路段 5:主管单位 6:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SignalLampProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SignalLampCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SignalLampArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1") && sQuery[4].equals("-1")) {
            hql += " and (SignalLampStartRoad ='" + sQuery[3] + "' or SignalLampEndRoad ='" + sQuery[3] + "')";
        }
        if (sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and (SignalLampEndRoad ='" + sQuery[4] + "' or SignalLampStartRoad ='" + sQuery[4] + "')";
        }
        if (!sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and ((SignalLampStartRoad ='" + sQuery[3] + "' and SignalLampEndRoad ='" + sQuery[4] + "') or" +
                    "(SignalLampStartRoad = '" + sQuery[4] + "' and SignalLampEndRoad = '" + sQuery[3] + "'))";
        }
        if (!sQuery[5].equals("-1")) {
            hql += " and SignalLampMainOrgId = '" + sQuery[5] + "'";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SignalLampOrgId = '" + sQuery[6] + "'";
        }
        if (actor != 1) {
            hql += " and (SignalLampOrgId IN (" + orgIds + ") or " +
                    "SignalLampMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<String> getReportCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select s.LampTime from tb_lamp_fault s where " +
                "(s.LampTime between '" + year + "' and '" + yearNext + "') and  (s.LampOrgId in (" + orgIds + ") or " +
                "s.LampMainOrgId in (" + orgIds + "))";
        Query query = em.createNativeQuery(hql);
        List<String> times = query.getResultList();
        return times;
    }

    public List<FaultCountDto> getExpenseCounts(Integer year, String orgIds) throws Exception {
        Integer yearNext = year + 1;
        String hql = "select m.LampMaintenanceTime AS maintenanceTime,m.LampMoney AS repairAmount from tb_lamp_fault m,tb_user u where " +
                "(m.LampMaintenanceTime between '" + year + "' and '" + yearNext + "') and m.LampMaintainerId = u.userId and u.orgId in (" + orgIds + ")";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(FaultCountDto.class));
        List<FaultCountDto> maintenanceDtos = query.getResultList();
        return maintenanceDtos;
    }

    public List<SignalLampFaultDto> getFault4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select f.LampFaultId,f.SignalLampId,f.LampPhoto,p.SignalLampPhoto,f.LampLevel,f.LampCheck,f.LampMainCheck,\n" +
                "f.LampTime,f.LampCheckTime,o.OrgName AS lampOrgName,o2.orgName as lampMainOrgName,p.SignalLampPeriod," +
                " p.SignalLampStartRoad,p.SignalLampEndRoad,p.signalLampType,p.signalLampServiceLife,p.signalLampBrand,\n" +
                "p.SignalLampArea,p.SignalLampCity,p.SignalLampProvince,u.UserName AS signalLampCreatorName\n" +
                "from tb_lamp_fault f,tb_org o,tb_org o2,tb_signal_lamp p,tb_user u\n" +
                "where f.LampOrgId = o.OrgId and p.SignalLampMainOrgId=o2.orgId and f.SignalLampId = p.SignalLampId  AND\n" +
                "f.LampCreatorId = u.UserId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.LampTime between '" + dateStr[0] + "' and '" + dateStr[1]+1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.LampOrgId IN (" + orgIds + ") or " +
                    "f.LampMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY f.LampTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        List<SignalLampFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getFaultCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select count(*) from tb_lamp_fault f,tb_org o WHERE f.LampOrgId= o.OrgId";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and f.LampTime between '" + dateStr[0] + "' and '" + dateStr[1]+1 + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (f.LampOrgId IN (" + orgIds + ") or " +
                    "f.LampMainOrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<SignalLampFaultDto> getDevice4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select m.LampFaultId,s.SignalLampType,m.LampPhoto,m.LampMaintenancePhoto,m.LampMaintenanceStatus,m.LampMaintenanceTime,s.signalLampServiceLife,\n" +
                "o.OrgName AS lampOrgName,m.SignalLampId,s.SignalLampEndRoad,s.SignalLampStartRoad,s.SignalLampArea,s.signalLampBrand,LampMoney,\n" +
                "s.SignalLampCity,s.SignalLampProvince,o2.orgName as lampMainOrgName,s.SignalLampPhoto,s.SignalLampPeriod,u.UserName AS signalLampCreatorName\n" +
                "from tb_lamp_fault m,tb_user u,tb_org o,tb_org o2,tb_signal_lamp s \n" +
                "where m.LampMaintenanceStatus=2 and m.LampMaintainerId = u.userId and s.SignalLampMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SignalLampId = s.SignalLampId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.LampMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY m.LampMaintenanceTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampFaultDto.class));
        List<SignalLampFaultDto> roadpoleDto = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return roadpoleDto;
    }

    public Integer getDeviceCount4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds, Integer userId) throws Exception {
        String hql = "select count(*) from tb_lamp_fault m,tb_user u,tb_org o,tb_org o2,tb_signal_lamp s \n" +
        "where m.LampMaintenanceStatus=2 and m.LampMaintainerId = u.userId and s.SignalLampMainOrgId=o2.orgId\n" +
                "and u.OrgId = o.OrgId and m.SignalLampId = s.SignalLampId ";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        String timeLimit = sQuery[0];
        if (!timeLimit.equals("-1") && timeLimit != "") {
            String[] dateStr = timeLimit.split(" - ");
            hql += " and m.LampMaintenanceTime between '" + dateStr[0] + "' and '" + dateStr[1] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and o.OrgId = " + sQuery[1];
        }
        if (actor != 1) {
            hql += " and (u.OrgId IN (" + orgIds + "))";
        }
        Query query = em.createNativeQuery(hql);
        Integer count = Integer.parseInt(query.getSingleResult().toString());
        return count;
    }

    public List<Integer> getSignalLampTypeCounts(String orgIds, String time, String province, String city, String area, String road) throws Exception{
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select r.SignalLampType from tb_signal_lamp r  where (r.SignalLampOrgId in (" + orgIds + ") or " +
                "r.SignalLampMainOrgId in (" + orgIds + ")) AND (r.SignalLampInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and r.SignalLampProvince like '%" + province + "%' and r.SignalLampCity like '%" + city + "%' " +
                " and r.SignalLampArea like '%" + area + "%'    ORDER BY r.SignalLampType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }
    public List<SignalLampDto> getLamp4DT(DTRequestDto dtRequestDto, Integer actor, String orgIds) throws Exception {
        String hql = "select sl.SignalLampId,sl.SignalLampPhoto,sl.SignalLampProvince,sl.SignalLampCity," +
                "sl.SignalLampArea,m.OrgName as signalLampMainOrgName,o.OrgName as signalLampOrgName,sl.SignalLampLongitude," +
                "sl.SignalLampLatitude,sl.SignalLampType,sl.SignalLampBrand,sl.SignalLampStartRoad,sl.SignalLampEndRoad," +
                "sl.SignalLampInstallTime,sl.SignalLampIsComplete" +
                " from tb_signal_lamp sl " +
                "LEFT JOIN tb_org m ON sl.SignalLampMainOrgId = m.OrgId " +
                "LEFT JOIN tb_org o ON sl.SignalLampOrgId = o.OrgId " +
                "where sl.SignalLampId LIKE '%'";
        String[] sQuery = convertParams(dtRequestDto.getSearch());
        //0:省 1:市 2:区域 3:起始路段 4:终止路段 5:主管单位 6:养护公司
        if (!sQuery[0].equals("-1")) {
            hql += " and SignalLampProvince = '" + sQuery[0] + "'";
        }
        if (!sQuery[1].equals("-1")) {
            hql += " and SignalLampCity = '" + sQuery[1] + "'";
        }
        if (!sQuery[2].equals("-1")) {
            hql += " and SignalLampArea = '" + sQuery[2] + "'";
        }
        if (!sQuery[3].equals("-1") && sQuery[4].equals("-1")) {
            hql += " and (SignalLampStartRoad ='" + sQuery[3] + "' or SignalLampEndRoad ='" + sQuery[3] + "')";
        }
        if (sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and (SignalLampEndRoad ='" + sQuery[4] + "' or SignalLampStartRoad ='" + sQuery[4] + "')";
        }
        if (!sQuery[3].equals("-1") && !sQuery[4].equals("-1")) {
            hql += " and ((SignalLampStartRoad ='" + sQuery[3] + "' and SignalLampEndRoad ='" + sQuery[4] + "') or" +
                    "(SignalLampStartRoad = '" + sQuery[4] + "' and SignalLampEndRoad = '" + sQuery[3] + "'))";
        }
        if (!sQuery[5].equals("-1")) {
            hql += " and SignalLampMainOrgId = '" + sQuery[5] + "'";
        }
        if (!sQuery[6].equals("-1")) {
            hql += " and SignalLampOrgId = '" + sQuery[6] + "'";
        }
        if (actor != 1) {
            hql += " and (SignalLampOrgId IN (" + orgIds + ") or " +
                    "SignalLampMainOrgId IN (" + orgIds + "))";
        }
        hql += " ORDER BY sl.SignalLampInstallTime DESC";
        Query query = em.createNativeQuery(hql);
        query.unwrap(NativeQuery.class).setResultTransformer(Transformers.aliasToBean(SignalLampDto.class));
        List<SignalLampDto> signalLampDtos = query.setFirstResult(dtRequestDto.getStart()).setMaxResults(dtRequestDto.getLength()).getResultList();
        return signalLampDtos;
    }

    public List<String> getProvinceName(String orgIds) throws Exception {
        String hql = "select distinct sl.SignalLampProvince from tb_signal_lamp sl where sl.SignalLampOrgId in (" + orgIds + ") or " +
                "sl.SignalLampMainOrgId in (" + orgIds + ")";
        hql += " order by convert(sl.SignalLampProvince using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> province = query.getResultList();
        return province;
    }

    public List<String> getCityName(String orgIds, String provinceName) throws Exception {
        String hql = "select distinct sl.SignalLampCity from tb_signal_lamp sl where (sl.SignalLampOrgId in (" + orgIds + ") or " +
                "sl.SignalLampMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sl.SignalLampProvince = '" + provinceName + "'";
        }
        hql += " order by convert(sl.SignalLampCity using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> city = query.getResultList();
        return city;
    }

    public List<String> getAllAreaName(String orgIds, String provinceName, String cityName) throws Exception {
        String hql = "select distinct sl.SignalLampArea from tb_signal_lamp sl where (sl.SignalLampOrgId IN (" + orgIds + ")" +
                " or sl.SignalLampMainOrgId IN (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sl.SignalLampProvince ='" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sl.SignalLampCity ='" + cityName + "'";
        }
        hql += " order by convert(sl.SignalLampArea using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> area = query.getResultList();
        return area;
    }


    public List<String> getStartRoadName(String orgIds, String provinceName, String cityName, String areaName,
                                         String endRoadName) throws Exception {
        String hql = "select distinct sl.SignalLampStartRoad from tb_signal_lamp sl where (sl.SignalLampOrgId in (" + orgIds + ") or " +
                "sl.SignalLampMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sl.SignalLampProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sl.SignalLampCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and sl.SignalLampArea = '" + areaName + "'";
        }
        if (!endRoadName.equals("-1")) {
            hql += " and sl.SignalLampEndRoad = '" + endRoadName + "'";
        }
        hql += " order by convert(sl.SignalLampStartRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<String> getEndRoadName(String orgIds, String startRoad, String provinceName, String cityName,
                                       String areaName) throws Exception {
        String hql = "select distinct sl.SignalLampEndRoad from tb_signal_lamp sl where (sl.SignalLampOrgId in (" + orgIds + ") or " +
                "sl.SignalLampMainOrgId in (" + orgIds + "))";
        if (!provinceName.equals("-1")) {
            hql += " and sl.SignalLampProvince = '" + provinceName + "'";
        }
        if (!cityName.equals("-1")) {
            hql += " and sl.SignalLampCity = '" + cityName + "'";
        }
        if (!areaName.equals("-1")) {
            hql += " and sl.SignalLampArea = '" + areaName + "'";
        }
        if (!startRoad.equals("-1")) {
            hql += " and sl.SignalLampStartRoad = '" + startRoad + "'";
        }
        hql += " order by convert(sl.SignalLampEndRoad using gbk) asc";
        Query query = em.createNativeQuery(hql);
        List<String> road = query.getResultList();
        return road;
    }

    public List<Integer> getSignalLampTypeCounts(String orgIds, String time, String province, String city, String area) throws Exception {
        String[] timeLimit = {"", ""};
        timeLimit = time.split(" - ");
        String hql = "select sl.SignalLampType from tb_signal_lamp sl  where (sl.SignalLampOrgId in (" + orgIds + ") or " +
                "sl.SignalLampMainOrgId in (" + orgIds + ")) AND (sl.SignalLampInstallTime between '" + timeLimit[0] + "' and '" + timeLimit[1] + "') " +
                " and sl.SignalLampProvince like '%" + province + "%' and sl.SignalLampCity like '%" + city + "%' " +
                " and sl.SignalLampArea like '%" + area + "%'    ORDER BY sl.SignalLampType";
        Query query = em.createNativeQuery(hql);
        List<Integer> counts = query.getResultList();
        return counts;
    }

}
